use crate::cache::RECORD_COUNT_CACHE;
use crate::keys::{BoundTypeKeyTrait, IndexTypeKeyTrait, UniqueTypeKeyTrait, VddKey};
use futures::TryStreamExt;
use regex::Regex;
#[cfg(feature = "postgres")]
use sqlx::PgConnection;
use sqlx::{Connection, MySqlConnection, Row};
use std::collections::HashMap;
use std::sync::{Arc, LazyLock};
use visit_dd_core::field::{VddField, VddFieldRef};
use visit_dd_core::model::{DataStore, DataStoreEngine, VddModel, VddSchema};
use visit_dd_core::proj::{DataSourceTrait, VddUser};
use visit_dd_core::query::{QueryDataPage, QueryPlan, ShareQueryParams, WebQueryParams};
use visit_dd_core::record::VddDisplay;
use visit_dd_core::{Ares, Astr, FieldId, RuntimeCache};

mod sqlx_basic_mysql;
#[cfg(feature = "postgres")]
mod sqlx_basic_pg;

static SQL_MAP: LazyLock<HashMap<Astr, Astr>> = LazyLock::new(|| {
    let mut map: HashMap<Astr, Astr> = HashMap::new();
    let txt = include_str!("basic.sql");
    let mut sql_name = String::new();
    txt.lines().for_each(|l| {
        if l.starts_with("-- ") {
            sql_name = l.replace("-- ", "");
        } else if l.len() > 0 {
            map.insert(sql_name.clone().into(), l.into());
        }
    });
    map
});

fn get_sql(name: &str, engine: DataStoreEngine) -> String {
    let sql = SQL_MAP.get(&Arc::from(name));
    let mut sql = if sql.is_some() {
        sql.unwrap().to_string()
    } else {
        panic!("no sql named {}", name)
    };
    match engine {
        DataStoreEngine::Mysql => {
            // 将sql语句模板中的$x全部替换成问号
            let re = Regex::new(r"\$\d+").unwrap();
            sql = re.replace_all(&sql, "?").to_string();
        }
        #[cfg(feature = "postgres")]
        _ => {}
    }
    sql
}

/// sqlx操作元数据方法定义
pub(crate) trait SqlxDefineTrait {
    // async fn insert_entity(&self, id: u64, entity_type: &EntityType, body: &str) -> Ares;
    async fn update_entity(&self, id: u64, body: &str) -> Ares;
    async fn load_entity(&self, id: u64) -> Result<Astr, Astr>;
    async fn load_entities(&self, vk_e: VddKey, deleted: bool) -> Result<Vec<Astr>, Astr>;
    async fn load_entities_form_index_query(
        &self,
        vk_e: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        deleted: bool,
    ) -> Result<Vec<Astr>, Astr>;
    async fn count_entities(&self, vk_e: VddKey) -> Result<usize, Astr>;
    async fn delete_entity(&self, id: u64) -> Ares;
    async fn delete_entities_from_index_query(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Ares;
    async fn add_to_index(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        index_item: u64,
    ) -> Ares;
    async fn delete_index(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        index_item: u64,
    ) -> Ares;
    async fn delete_indexes(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Ares;
    async fn read_index(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Vec<i64>;
    async fn count_in_index(
        &self,
        vk_i: impl IndexTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Result<usize, Astr>;
    async fn get_unique(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Result<u64, Astr>;
    async fn delete_unique_by_value(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        link_id: u64,
    ) -> Ares;
    async fn delete_uniques_from_index_query(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        vk_i: VddKey,
    ) -> Ares;
    async fn select_uniques(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> HashMap<String, u64>;
    async fn select_uniques_by_value(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        link_id: Option<u64>,
    ) -> HashMap<String, u64>;
    async fn select_uniques_from_index_query(
        &self,
        vk_u: impl UniqueTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        vk_i: VddKey,
    ) -> HashMap<String, u64>;
    async fn save_bound(
        &self,
        vk: impl BoundTypeKeyTrait + Into<u16> + AsRef<VddKey>,
        bound_content: &str,
    ) -> Ares;
    /// 多个绑定，根据绑定类型和绑定左值，绑定右值可选, 如果左值为0,左值失效，绑定类型永远有效
    async fn select_bounds(
        &self,
        vk: impl BoundTypeKeyTrait + Into<u16> + AsRef<VddKey>,
    ) -> Vec<String>;
    /// 删除多个绑定
    async fn delete_bounds(&self, vk: impl BoundTypeKeyTrait + Into<u16> + AsRef<VddKey>) -> Ares;
    // async fn count_bounds(&self, bound_type: &BoundType, bound_right: Option<u64>) -> usize;
    /// 按绑定值的左侧值进行分组计数
    async fn count_bounds_grouped(&self, vk_b_s: &[VddKey]) -> HashMap<u64, usize>;
    // async fn count_bounds_by_right(&self, bound_type: &BoundType, bound_right: u64) -> usize;
    // 以下是需要手动控制事务的操作
    async fn u_register(&self, user: &VddUser) -> Ares;
    async fn m_save_new(&self, model: &VddModel) -> Ares;
    async fn m_save_tags(
        &self,
        model: &VddModel,
        model_tag: &str,
        tag_map: &HashMap<Astr, u64>,
    ) -> Ares;
    async fn f_save_new(&self, field: &VddField) -> Ares;
    async fn f_delete(&self, model_id: u64, field_id: FieldId) -> Ares;
    async fn m_clear_tags(&self, model_id: u64, field_ids: &[FieldId]) -> Ares;
    async fn do_delete_entity(&self, id: u64) -> Ares;
    async fn restore_entity(&self, id: u64) -> Ares;
    async fn check_entity_deleted(&self, id: u64) -> bool;
    async fn save_plan(&self, plan: &QueryPlan, is_update: bool) -> Ares;
}

pub trait SqlxDataStoreTrait {
    /// 测试连接配置是否正确
    fn test_connection(&self) -> impl std::future::Future<Output = bool> + Send;
    fn import_fields(
        &self,
        model: &VddModel,
    ) -> impl std::future::Future<Output = Vec<VddFieldRef>> + Send;
    fn count_records(
        &self,
        model_id: u64,
    ) -> impl std::future::Future<Output = Result<usize, Astr>> + Send;
    fn sqlx_get_page(
        &self,
        wqp: &WebQueryParams,
        schema: &VddSchema,
    ) -> impl std::future::Future<Output = QueryDataPage<VddDisplay>> + Send;
    fn sqlx_share_page(
        &self,
        sqp: &ShareQueryParams,
    ) -> impl std::future::Future<Output = QueryDataPage<VddDisplay>> + Send;
}

impl SqlxDataStoreTrait for DataStore {
    async fn test_connection(&self) -> bool {
        match self.store_engine {
            #[cfg(feature = "postgres")]
            DataStoreEngine::Postgres => sqlx_basic_pg::test_connection(self).await,
            DataStoreEngine::Mysql => sqlx_basic_mysql::test_connection(self).await,
        }
    }
    async fn import_fields(&self, model: &VddModel) -> Vec<VddFieldRef> {
        match self.store_engine {
            #[cfg(feature = "postgres")]
            DataStoreEngine::Postgres => sqlx_basic_pg::table_to_fields(model).await,
            DataStoreEngine::Mysql => sqlx_basic_mysql::table_to_fields(model).await,
        }
    }
    async fn count_records(&self, model_id: u64) -> Result<usize, Arc<str>> {
        let record_count = LazyLock::force(&RECORD_COUNT_CACHE)
            .read()
            .unwrap()
            .get(&model_id)
            .map(|x| {
                if x.test_alive(120) {
                    Some(x.data)
                } else {
                    None
                }
            });
        if record_count.is_none() || record_count.unwrap().is_none() {
            let mut record_count = 0usize;
            let sql = format!("SELECT COUNT(*) AS c FROM {}", self.remote_key);
            match self.store_engine {
                #[cfg(feature = "postgres")]
                DataStoreEngine::Postgres => {
                    let mut c = PgConnection::connect(&self.to_url()).await.unwrap();
                    let mut rows = sqlx::query(&sql).fetch(&mut c);
                    if let Some(row) = rows.try_next().await.unwrap() {
                        record_count = row.try_get::<i64, &str>("c").unwrap() as usize;
                    }
                }
                DataStoreEngine::Mysql => {
                    let mut c = MySqlConnection::connect(&self.to_url()).await.unwrap();
                    let mut rows = sqlx::query(&sql).fetch(&mut c);
                    if let Some(row) = rows.try_next().await.unwrap() {
                        record_count = row.try_get::<i64, &str>("c").unwrap() as usize;
                    }
                }
            }
            LazyLock::force(&RECORD_COUNT_CACHE)
                .write()
                .unwrap()
                .insert(model_id, RuntimeCache::<usize>::from(record_count));
            Ok(record_count)
        } else {
            Ok(record_count.unwrap().unwrap())
        }
    }
    async fn sqlx_get_page(
        &self,
        wqp: &WebQueryParams,
        schema: &VddSchema,
    ) -> QueryDataPage<VddDisplay> {
        match self.store_engine {
            #[cfg(feature = "postgres")]
            DataStoreEngine::Postgres => sqlx_basic_pg::get_page_sqlx(schema, wqp).await,
            DataStoreEngine::Mysql => sqlx_basic_mysql::get_page_sqlx(schema, wqp).await,
        }
    }
    async fn sqlx_share_page(&self, sqp: &ShareQueryParams) -> QueryDataPage<VddDisplay> {
        match self.store_engine {
            #[cfg(feature = "postgres")]
            DataStoreEngine::Postgres => sqlx_basic_pg::share_page(sqp).await,
            DataStoreEngine::Mysql => sqlx_basic_mysql::share_page(sqp).await,
        }
    }
}
